package  edu.psu.collegerecommendationtool.coreservlets;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.faces.bean.*;

import edu.psu.collegerecommendationtool.commonutils.MySQLInfo;
import edu.psu.collegerecommendationtool.model.AccountInfo;
import edu.psu.collegerecommendationtool.model.DemographicInfo;
import edu.psu.collegerecommendationtool.model.EducationInfo;
import edu.psu.collegerecommendationtool.model.PreferenceInfo;
import edu.psu.collegerecommendationtool.model.CollegeResult;
import edu.psu.collegerecommendationtool.model.UserBean;


@ManagedBean(name="loginBean")
public class LoginController {
   @ManagedProperty(value="#{userBean}")
   private UserBean user;

   private String entered_username;
   private String entered_password;

   public UserBean getUser() {
      return user;
   }
   public void setUser(UserBean user) {
      this.user = user;
   }

   public String getEntered_username() {
      return entered_username;
   }
   public void setEntered_username(String entered_username) {
      this.entered_username = entered_username;
   }

   public String getEntered_password() {
      return entered_password;
   }
   public void setEntered_password(String entered_password) {
      this.entered_password = entered_password;
   }

////////////////////////////////////////////////////////////////////////

   public String loginAction() throws ClassNotFoundException {
      MySQLInfo mysqlinfo = new MySQLInfo();
      String DB_URL = mysqlinfo.getDBUrl();

      boolean validLogin = false;
      String loginresult = "";
      String sql = "SELECT * FROM " + mysqlinfo.getDbName() + "." + mysqlinfo.getAcctable() + 
                   " WHERE  USERNAME = '" + entered_username + "';";

      Class.forName("com.mysql.jdbc.Driver");
      validLogin = loginValidityCheck(sql, DB_URL);

      if(true == validLogin) {
         sql = "SELECT * FROM " + mysqlinfo.getDbName() + "." + mysqlinfo.getDemtable() + 
               " WHERE  USER_ID = '" + user.getUser_id() + "';";
         storeDemographic(sql, DB_URL);

         sql = "SELECT * FROM " + mysqlinfo.getDbName() + "." + mysqlinfo.getEdutable() + 
               " WHERE  USER_ID = '" + user.getUser_id() + "';";
         storeEducation(sql, DB_URL);

         sql = "SELECT * FROM " + mysqlinfo.getDbName() + "." + mysqlinfo.getPretable() + 
               " WHERE  USER_ID = '" + user.getUser_id() + "';";
         storePreference(sql, DB_URL);

         sql = "SELECT * FROM " + mysqlinfo.getDbName() + "." + mysqlinfo.getRestable() + 
                 " WHERE  USER_ID = '" + user.getUser_id() + "';";
         storeCollege(sql, DB_URL);


         loginresult = "MainPage";

      }
      else {
         loginresult = "LoginErrorPage";
      }
	          
      return(loginresult);
   }

////////////////////////////////////////////////////////////////////////

   public boolean loginValidityCheck(String sql, String DB_URL) {
      long user_id = 0;
      String un = "";
      String pw = "";
      String fn = "";
      String ln = "";
      String email = "";
      boolean validLogin = false;

      AccountInfo accountInfo = new AccountInfo();

      ResultSet result = null;
      PreparedStatement pst = null;
      Connection con = getConnection(DB_URL);

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         while (result.next()) {
            user_id = result.getLong(1);
            un = result.getString(2);
            pw = result.getString(3);
            fn = result.getString(4);
            ln = result.getString(5);
            email = result.getString(6);
         }

         if ((un.equals(entered_username)) && (pw.equals(entered_password))) {
            accountInfo.setUsername(un);
            accountInfo.setPassword(pw);
            accountInfo.setFirstname(fn);
            accountInfo.setLastname(ln);
            accountInfo.setEmail(email);

            user.setUser_id(user_id);
            user.setAccountInfo(accountInfo);

            validLogin = true;

         }

         result.close();
         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

      return(validLogin);
   }

////////////////////////////////////////////////////////////////////////

   public void storeDemographic(String sql, String DB_URL) {
      DemographicInfo demographicInfo = new DemographicInfo();

      ResultSet result = null;
      PreparedStatement pst = null;
      Connection con = getConnection(DB_URL);

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         while (result.next()) {
            demographicInfo.setState(result.getString(2));
            demographicInfo.setAge(result.getByte(3));
            demographicInfo.setSex(result.getString(4));
            demographicInfo.setRace(result.getString(5));
            demographicInfo.setRelative1(result.getString(6));
            demographicInfo.setCollege1(result.getString(7));
            demographicInfo.setRelative2(result.getString(8));
            demographicInfo.setCollege2(result.getString(9));
            demographicInfo.setRelative3(result.getString(10));
            demographicInfo.setCollege3(result.getString(11));
            demographicInfo.setRelative4(result.getString(12));
            demographicInfo.setCollege4(result.getString(13));
            demographicInfo.setRelative5(result.getString(14));
            demographicInfo.setCollege5(result.getString(15));

         }

         user.setDemographicInfo(demographicInfo);

         result.close();
         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

   }

////////////////////////////////////////////////////////////////////////

   public void storeEducation(String sql, String DB_URL) {
      EducationInfo educationInfo = new EducationInfo();

      ResultSet result = null;
      PreparedStatement pst = null;
      Connection con = getConnection(DB_URL);

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         while (result.next()) {
            educationInfo.setGPA(result.getFloat(2));
            educationInfo.setMathSAT(result.getShort(3));
            educationInfo.setCreadSAT(result.getShort(4));
            educationInfo.setWriteSAT(result.getShort(5));
            educationInfo.setMathACT(result.getByte(6));
            educationInfo.setReadACT(result.getByte(7));
            educationInfo.setEnglishACT(result.getByte(8));
            educationInfo.setScienceACT(result.getByte(9));

         }

         user.setEducationInfo(educationInfo);

         result.close();
         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

   }

////////////////////////////////////////////////////////////////////////

   public void storePreference(String sql, String DB_URL) {
      PreferenceInfo preferenceInfo = new PreferenceInfo();

      ResultSet result = null;
      PreparedStatement pst = null;
      Connection con = getConnection(DB_URL);

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         while (result.next()) {
            preferenceInfo.setLocation(result.getString(2));
            preferenceInfo.setPubpri(result.getString(3));
            preferenceInfo.setSize(result.getString(4));
            preferenceInfo.setSexratio(result.getString(5));

         }

         user.setPreferenceInfo(preferenceInfo);

         result.close();
         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

   }

////////////////////////////////////////////////////////////////////////

   public void storeCollege(String sql, String DB_URL) {
      CollegeResult resultList = new CollegeResult();

      ResultSet result = null;
      PreparedStatement pst = null;
      Connection con = getConnection(DB_URL);

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         while (result.next()) {
        	resultList.setActive(result.getByte(2));
        	resultList.setCollege1(result.getString(3));
        	resultList.setResult1(result.getString(4));
            resultList.setCollege2(result.getString(5));
            resultList.setResult2(result.getString(6));
            resultList.setCollege3(result.getString(7));
            resultList.setResult3(result.getString(8));
            resultList.setCollege4(result.getString(9));
            resultList.setResult4(result.getString(10));
            resultList.setCollege5(result.getString(11));
            resultList.setResult5(result.getString(12));

         }

         user.setResultList(resultList);

         result.close();
         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

   }

////////////////////////////////////////////////////////////////////////

   public Connection getConnection(String DB_URL) {
      Connection con = null;

      try {
         con = DriverManager.getConnection(DB_URL);
      } catch (SQLException ex) {
         System.out.println(ex.getMessage());
      }
      finally { }

      return con;
   }

////////////////////////////////////////////////////////////////////////

   public String returnLogin() {
      return("LoginPage");
   }

////////////////////////////////////////////////////////////////////////

   public String registerPage() {
      return("RegistrationPage");
   }
}